




**************************Part III: Construct the measure of bank specialization

***Main Measure of Industry Specialization
{
clear
cd "~/DataFolder"

***Outlying weight relative to peer banks
use  dealscan_facility3, clear
drop lenderid lender

*Sort and Expand
order rssdid facid facstryear facendyear
sort rssdid facid year
duplicates drop rssdid year facid, force

*Merge with Compustat
merge m:1 gvkey year using compustat_cleaned, keepusing(naicsh naics2 naics3 sich)
keep if _merge==3
drop _merge
drop if rssdid==.

*Exclude NAICS 52 - Finance and Insurance
drop if missing(naicsh)
drop if naics2=="52" 

*Agregate at rssdid-year-naics3
*simple average
sort rssdid year naics3
bysort rssdid year naics3: egen tfacamt=total(facilityamt)
bysort rssdid year: egen t2facamt=total(facilityamt)

duplicates drop rssdid year naics3, force

gen bankspec=tfacamt/t2facamt

bysort naics3 year: egen P75=pctile(bankspec), p(75)
bysort naics3 year: egen P25=pctile(bankspec), p(25)
gen inter_q=P75-P25
gen threshold=P75+1.5*inter_q

gen spec = 1 if bankspec>=threshold
replace spec=0 if missing(spec)
gen bankspec_above=bankspec-threshold

keep if spec==1

keep rssdid year naics3 spec bankspec_above 

duplicates drop rssdid year naics3, force   
destring naics3, replace
sort rssdid year
save KeyMeasure, replace
}
*


*Robust: Weighted Specialization
{
clear
cd "~/DataFolder"

use  dealscan_facility3
drop lenderid lender

*Sort and Expand
order rssdid facid facstryear facendyear
sort rssdid facid year
duplicates drop rssdid year facid, force

*Merge with Compustat
merge m:1 gvkey year using compustat_cleaned, keepusing(naicsh naics2 naics3 sich size_qtl)
drop if missing(size_qtl)
keep if _merge==3
drop _merge
drop if rssdid==.

*Exclude NAICS 52 - Finance and Insurance
drop if missing(naicsh)
drop if naics2=="52" 

*Agregate at rssdid-year-naics3
*weighted average (lending to larger firms has higher weight)
gen facilityamt_wt=facilityamt*size_qtl
bysort rssdid year naics3: egen tfacamt_wt=total(facilityamt_wt)
bysort rssdid year: egen t2facamt_wt=total(facilityamt_wt)

duplicates drop rssdid year naics3, force

gen bankspec_wt=tfacamt_wt/t2facamt_wt

bysort naics3 year: egen P75=pctile(bankspec_wt), p(75)
bysort naics3 year: egen P25=pctile(bankspec_wt), p(25)
gen inter_q=P75-P25
gen threshold=P75+1.5*inter_q

gen spec=1 if bankspec_wt>=threshold 
replace spec=0 if missing(spec)
gen bankspec_above=bankspec_wt-threshold

keep if spec==1

keep rssdid year naics3 spec bankspec_above
duplicates drop rssdid year naics3, force 
destring naics3, replace
sort rssdid year
save KeyMeasure_wt, replace
}
*

